/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.PersonDTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Arturia
 */
public class PersonDAO {

    public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(person_id) = '1' THEN CAST(person_id AS INT) "
                    + "ELSE NULL END FROM t_person) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public static ArrayList LoadDatabase(Connection con, PersonDTO person) {
        ArrayList<PersonDTO> result = new ArrayList<PersonDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (person == null) {
                sql = "SELECT * FROM t_person";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                PersonDTO personTemp = new PersonDTO();
                personTemp = new PersonDTO();

                personTemp.setStrPersonID(rs.getString(1));

                if (rs.wasNull()) {
                    personTemp.setStrPersonID(generateID(con));
                }

                personTemp.setStrGender(rs.getString(2));

                if (rs.wasNull()) {
                    personTemp.setStrGender("Male");
                }

                personTemp.setStrFirstName(rs.getString(3));

                if (rs.wasNull()) {
                    personTemp.setStrFirstName("");
                }

                personTemp.setStrLastName(rs.getString(4));

                if (rs.wasNull()) {
                    personTemp.setStrLastName("");
                }

                personTemp.setDtDOB(new java.util.Date(rs.getTimestamp(5).getTime()));

                if (rs.wasNull()) {
                    personTemp.setDtDOB(new java.util.Date());
                }

                personTemp.setStrPlaceOfBirth(rs.getString(6));

                if (rs.wasNull()) {
                    personTemp.setStrPlaceOfBirth("");
                }

                personTemp.setStrAddress(rs.getString(7));

                if (rs.wasNull()) {
                    personTemp.setStrAddress("");
                }

                personTemp.setStrPhone(rs.getString(8));

                if (rs.wasNull()) {
                    personTemp.setStrPhone("");
                }

                result.add(personTemp);
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, PersonDTO person) {
        int result = 0;
        try {
            String sql = "INSERT INTO t_person VALUES(?,?,?,?,?,?,?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, person.getStrPersonID());
            stm.setString(2, person.getStrGender());
            stm.setString(3, person.getStrFirstName());
            stm.setString(4, person.getStrLastName());
            stm.setDate(5, (Date) person.getDtDOB());
            stm.setString(6, person.getStrPlaceOfBirth());
            stm.setString(7, person.getStrAddress());
            stm.setString(8, person.getStrPhone());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, PersonDTO person) {
        int result = 0;
        try {
            String query = "UPDATE t_person SET gender=?, first_name=?, last_name=?, "
                    + "date_of_birth=?, place_of_birth=?, current_address=?, phone_number=? "
                    + "WHERE person_id=?";
            PreparedStatement stm = con.prepareStatement(query);
            
            stm.setString(1, person.getStrGender());
            stm.setString(2, person.getStrFirstName());
            stm.setString(3, person.getStrLastName());
            stm.setTimestamp(4, new java.sql.Timestamp(person.getDtDOB().getTime()));
            stm.setString(5, person.getStrPlaceOfBirth());
            stm.setString(6, person.getStrAddress());
            stm.setString(7, person.getStrPhone());
            stm.setString(8, person.getStrPersonID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String personID) {
        int result = 0;
        try {
            String sql = "DELETE FROM t_person WHERE person_id=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, personID);
            result = stm.executeUpdate();
            stm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
